<?php

/**
 * Object Relational Mapper.
 * Base static db manipulation methods
 * @package Nina
 * @author John Main <john@thedaddy.org>, Chris Corbyn
 */
abstract class Nina_Orm_Base
{
	protected static $cache = array();

	/**
	 * Check if a given table exists in the ORM system.
	 * @param string $class Class name of the table
	 * @return boolean
	 */
	public static function isTable($class) {
		if(class_exists($class)) {
			try {
				$table = new $class();
			}
			catch(Nina_Exception_Orm $e) {
				return false;
			}
		}

		return true;
	}

	/**
	 * Check if a given table field exists in this ORM system.
	 * @param string $table Table name (class name)
	 * @param string $field Field name (constant name)
	 * @return boolean
	 */
	public static function isField($table, $field) {
		return defined($table . "::" . $field);
	}

	/**
	 * Helper factory to create new instances of the Criteria.
	 * @return Nina_Orm_Criteria
	 */
	public static function newCriteria() {
		return new Nina_Orm_Criteria();
	}

	/**
	 * Helper function to create new instances of a Join
	 * @param array|string $left Left hand side
	 * @param array|string $right Right hand side
	 * @param string [$type=Nina_Orm_Join::INNER] Join type
	 * @return Nina_Orm_Join
	 */
	public static function newJoin($left, $right, $type = Nina_Orm_Join::INNER) {
		return new Nina_Orm_Join($left, $right, $type);
	}

	/**
	 * Get a record according to its primary key.
	 * @param Nina_Orm $obj The object type to fetch
	 * @param integer $pk Primary key value
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return Nina_Orm
	 * @throws Nina_Exception_Orm If bad mapping data is used
	 */
	public static function retrieveByPk(Nina_Orm $obj, $pk, $show_deleted = false) {
		$c = self::newCriteria();
		$c->setDb($obj->getDb());
		$c->add($obj->getPkField(), $pk);
		return self::doSelectOne($obj, $c, $show_deleted);
	}

	/**
	 * Update a record to set the given fields where the primary key matches the one given.
	 * @param Nina_Orm $obj The object type to update
	 * @param array $fields Fields to set (field => value)
	 * @param integer $pk Primary key
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return boolean
	 */
	public static function updateByPk(Nina_Orm $obj, array $fields, $pk, $show_deleted = false) {
		$c = self::newCriteria();
		$c->setDb($obj->getDb());
		$c->add($obj->getPkField(), $pk);
		return self::doUpdate($obj, $fields, $c, $show_deleted);
	}

	/**
	 * Delete a record based on it's primary key.
	 * @param Nina_Orm $obj The object type to delete
	 * @param integer $pk Primary key
	 * @return boolean
	 */
	public static function deleteByPk(Nina_Orm $obj, $pk) {
		$c = self::newCriteria();
		$c->setDb($obj->getDb());
		$c->add($obj->getPkField(), $pk);
		return self::doDelete($obj, $c, true);
	}

	/**
	 * Returns an SQL count
	 * @param Nina_Orm $obj The object type to count
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return integer
	 * @see doSelect
	 */
	public static function doCount(Nina_Orm $obj, Nina_Orm_Criteria $c, $show_deleted = false, $distinct = false) {
		$c->setLimit(1);
		if (!$stmt = self::createCountStatement($obj, $c, $show_deleted, $distinct))
			throw new Nina_Exception_Orm("Could not COUNT record for " . $obj->getClass());

		$result = $stmt->execute();
		if (!$result) return 0;
		if (!$row = $result->row()) return 0;
		return $row['row_count'];
	}

	/**
	 * Returns a distinct SQL count
	 * @param Nina_Orm $obj The object type to count
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param string $record Record name
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return integer
	 * @see doSelect
	 */
	public static function doCountDistinct(Nina_Orm $obj, Nina_Orm_Criteria $c, $record, $show_deleted = false) {
		$c->setLimit(1);
		if (!$stmt = self::createCountDistinctStatement($obj, $c, $record, $show_deleted))
			throw new Nina_Exception_Orm("Could not COUNT record for " . $obj->getClass());

		$result = $stmt->execute();
		if (!$result) return 0;
		if (!$row = $result->row()) return 0;
		return $row['row_count'];
	}

	/**
	 * Select all records from the database which adhere to the given criteria.
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @param boolean [$distinct=false] Do a distinct select
	 * @return array,Nina_Orm
	 */
	public static function doSelect(Nina_Orm $obj, Nina_Orm_Criteria $c, $show_deleted = false, $distinct = false) {

		if($obj->getAutoJoin() && !$distinct) {
			return self::doSelectJoin($obj, $c, $show_deleted);
		}
		else {
			$hash = md5(get_class($obj).serialize($c).($show_deleted ? 1 : 0).($distinct ? 1 : 0));

			if(isset(self::$cache[$hash]))
				return self::$cache[$hash];
			else {

				if (!$stmt = self::createSelectStatement($obj, $c, $show_deleted, $distinct))
					throw new Nina_Exception_Orm("Could not SELECT record for " . $obj->getClass() . ".");

				$result = $stmt->execute();

				if (!$result) return array();

				$ret = array();

				while ($row = $result->row()) {
					$o = clone $obj;
					foreach ($obj->getFieldNames() as $field) {
						$o->injectField($field, $row[$field]);
					}
					$ret[] = $o;
				}

				self::$cache[$hash] = $ret;

				return $ret;
			}
		}
	}

	/**
	 * Select distinctly all records from the database which adhere to the given criteria.
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param string $record Record name
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return array
	 */
	public static function doSelectDistinct(Nina_Orm $obj, Nina_Orm_Criteria $c, $record, $show_deleted = false) {

		$hash = md5(get_class($obj).serialize($c).($show_deleted ? 1 : 0).'dist');

		if(isset(self::$cache[$hash]))
			return self::$cache[$hash];
		else {

			if (!$stmt = self::createSelectDistinctStatement($obj, $c, $record, $show_deleted))
				throw new Nina_Exception_Orm("Could not SELECT record for " . $obj->getClass() . ".");

			$result = $stmt->execute();

			if (!$result) return array();

			$ret = array();

			while ($row = $result->row()) {
				$o = clone $obj;
				foreach ($obj->getFieldNames() as $field) {
					$o->injectField($field, $row[$field]);
				}
				$ret[] = $o;
			}

			self::$cache[$hash] = $ret;

			return $ret;
		}
	}

	/**
	 * Select random records from the database
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @return array
	 */
	public static function doSelectRandom(Nina_Orm $obj, Nina_Orm_Criteria $c) {
		$c->addOrderBy('random_index');

		$fields = array();
		foreach ($obj->getFieldNames() as $field) {
			$fields[] = $field . " AS `" . str_replace('`', '', $field) . "`";
		}

		$sql = "SELECT " . implode("," . PHP_EOL, $fields) . PHP_EOL;

		$sql .= ", FLOOR(1 + RAND() * max_pseudo_table.max_id) AS random_index" . PHP_EOL;
		$sql .= "FROM " . $obj->getTableName() . ", (SELECT MAX(id) - 1 as max_id FROM " . $obj->getTableName() . ") max_pseudo_table
WHERE " . $c->createWhereClause();// . "ORDER BY random_index";

		if (!$stmt = self::prepare($obj, $sql))
			throw new Nina_Exception_Orm("Could not SELECT record for " . $obj->getClass() . ".");

		$c->commit($stmt);
		$result = $stmt->execute();

		if (!$result) return array();

		$ret = array();

		while ($row = $result->row()) {
			$o = clone $obj;
			foreach ($obj->getFieldNames() as $field) {
				$o->injectField($field, $row[$field]);
			}
			$ret[] = $o;
		}

		return $ret;
	}

	/**
	 * Select one random record from the database
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @return Nina_Orm
	 */
	public static function doSelectRandomOne(Nina_Orm $obj, Nina_Orm_Criteria $c) {
		$c->setLimit(1);

		if($records = self::doSelectRandom($obj, $c))
			return $records[0];
		else
			return false;
	}


	/**
	 * Show the SQL produced by a set of select criteria
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @param boolean [$distinct=false] Do a distinct select
	 * @return string
	 */
	public static function getSelectSql(Nina_Orm $obj, Nina_Orm_Criteria $c, $show_deleted = false, $distinct = false) {

		if (!$stmt = self::createSelectStatement($obj, $c, $show_deleted, $distinct))
			throw new Nina_Exception_Orm("Could not SELECT record for " . $obj->getClass() . ".");

		return $stmt->getSql();
	}

	/**
	 * Show the SQL produced by a set of select distinct criteria
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param string $record Record name
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return string
	 */
	public static function getSelectDistinctSql(Nina_Orm $obj, Nina_Orm_Criteria $c, $record, $show_deleted = false) {

		if (!$stmt = self::createSelectDistinctStatement($obj, $c, $record, $show_deleted))
			throw new Nina_Exception_Orm("Could not SELECT record for " . $obj->getClass() . ".");

		return $stmt->getSql();
	}


	/**
	 * Show the SQL produced by a set of count criteria
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @param boolean [$distinct=false] Do a distinct select
	 * @return string
	 */
	public static function getCountSql(Nina_Orm $obj, Nina_Orm_Criteria $c, $show_deleted = false, $distinct = false) {

		if (!$stmt = self::createCountStatement($obj, $c, $show_deleted, $distinct))
			throw new Nina_Exception_Orm("Could not SELECT record for " . $obj->getClass() . ".");

		return $stmt->getSql();
	}

	/**
	 * This works the same as doSelect(), except that only the first record is returned
	 * rather than an entire resultset.
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return Nina_Orm
	 * @see doSelect
	 */
	public static function doSelectOne(Nina_Orm $obj, Nina_Orm_Criteria $c, $show_deleted = false) {
		$c->setLimit(1);

		if($record = self::doSelect($obj, $c, $show_deleted))
			return $record[0];
		else
			return false;
	}

	/**
	 * This works the same as doSelectOne(), except that if the first record is not found, a new one is created according to the given criteria and returned
	 * Uses helper methods to work on either an array or a criteria object
	 * @param Nina_Orm $obj Used for hydration
	 * @param array|Nina_Orm_Criteria $data The select/create data
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return Nina_Orm
	 * @see doSelect
	 */
	public static function doSelectOrCreate(Nina_Orm $obj, $data, $show_deleted = false) {
		if(@get_class($data) == 'Nina_Orm_Criteria')
			return self::doSelectOrCreateByCriteria($obj, $data, $show_deleted);
		else
			return self::doSelectOrCreateByArray($obj, $data, $show_deleted);
	}

	/**
	 * This works the same as doSelectOne(), except that if the first record is not found, a new one is created according to the given criteria and returned
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The select/create data
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return Nina_Orm
	 * @see doSelect
	 */
	public static function doSelectOrCreateByCriteria(Nina_Orm $obj, Nina_Orm_Criteria $c, $show_deleted = false) {
		if($record = self::doSelectOne($obj, $c, $show_deleted))
			return $record;
		else
		{
			$fields = $c->getFields();

			//Can only work with 'where', not 'having'
			foreach ($fields['where'] as $field) {
				list($model, $field_name) = explode('.', $field['field']);

				//Can only work with 'and', not 'or'
				if($field['andor'] == Nina_Orm_Criteria::OP_AND) {
					$method = 'set'.Nina_String::doCamelise($field_name);
					$obj->$method($field['value']);
				}
			}

			$obj->save();

			return $obj;
		}
	}

	/**
	 * This works the same as doSelectOne(), except that if the first record is not found, a new one is created according to the given criteria and returned
	 * @param Nina_Orm $obj Used for hydration
	 * @param array $data The select/create data
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return Nina_Orm
	 * @see doSelect
	 */
	public static function doSelectOrCreateByArray(Nina_Orm $obj, array $data, $show_deleted = false) {
		$c = self::newCriteria();

		foreach($data as $key => $value) {
			$c->add($key, $value);
		}

		if($record = self::doSelectOne($obj, $c, $show_deleted))
			return $record;
		else
			return self::doInsert($obj, $data);
	}

	/**
	 * Select all records along with everything they're joined to.
	 * The join used will be an INNER join unless the criteria passed defines otherwise.
	 * @param Nina_Orm $obj The table to start joining from
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @param boolean [$outer=false] Do outer join
	 * @return Nina_Orm
	 * @throws Nina_Exception_Orm If invalid mappings are found
	 */
	public static function doSelectJoin(Nina_Orm $obj, Nina_Orm_Criteria $c, $show_deleted = false, $outer = false) {

		$hash = md5(get_class($obj).serialize($c).($show_deleted ? 1 : 0).'join'.($outer ? 1 : 0));

		if(isset(self::$cache[$hash]))
			return self::$cache[$hash];
		else {
			if (!$stmt = self::createSelectJoinStatement($obj, $c, $show_deleted, $outer))
				throw new Nina_Exception_Orm("Could not SELECT joined records for " . $obj->getClass() . ".");

			$results = self::selectJoinHelper($obj, $stmt);
			self::$cache[$hash] = $results;

			return $results;
		}
	}

	/**
	 * Select all records along with everything they're joined to (inner join)
	 * The join used will be an INNER join unless the criteria passed defines otherwise.
	 * @param Nina_Orm $obj The table to start joining from
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return Nina_Orm
	 * @throws Nina_Exception_Orm If invalid mappings are found
	 */
	public static function doSelectJoinInner(Nina_Orm $obj, Nina_Orm_Criteria $c, $show_deleted = false) {
		return self::doSelectJoin($obj, $c, $show_deleted, false);
	}

	/**
	 * Select all records along with everything they're joined to (outer join)
	 * The join used will be an INNER join unless the criteria passed defines otherwise.
	 * @param Nina_Orm $obj The table to start joining from
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return Nina_Orm
	 * @throws Nina_Exception_Orm If invalid mappings are found
	 */
	public static function doSelectJoinOuter(Nina_Orm $obj, Nina_Orm_Criteria $c, $show_deleted = false) {
		return self::doSelectJoin($obj, $c, $show_deleted, true);
	}

	/**
	 * Distinct select all records along with everything they're joined to.
	 * The join used will be an INNER join unless the criteria passed defines otherwise.
	 * @param Nina_Orm $obj The table to start joining from
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param string $record Record name
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @param boolean [$outer=false] Do outer join
	 * @return Nina_Orm
	 * @throws Nina_Exception_Orm If invalid mappings are found
	 */
	public static function doSelectJoinDistinct(Nina_Orm $obj, Nina_Orm_Criteria $c, $record, $show_deleted = false, $outer = false) {
		$hash = md5(get_class($obj).serialize($c).($show_deleted ? 1 : 0).'join_distinct'.($outer ? 1 : 0));

		if(isset(self::$cache[$hash]))
			return self::$cache[$hash];
		else {
			if (!$stmt = self::createSelectJoinDistinctStatement($obj, $c, $record, $show_deleted, $outer))
				throw new Nina_Exception_Orm("Could not SELECT joined records for " . $obj->getClass() . ".");

			$results = self::selectJoinHelper($obj, $stmt);
			self::$cache[$hash] = $results;

			return $results;
		}
	}

	/**
	 * Distinct select all records along with everything they're joined to (inner join)
	 * The join used will be an INNER join unless the criteria passed defines otherwise.
	 * @param Nina_Orm $obj The table to start joining from
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param string $record Record name
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return Nina_Orm
	 * @throws Nina_Exception_Orm If invalid mappings are found
	 */
	public static function doSelectJoinDistinctInner(Nina_Orm $obj, Nina_Orm_Criteria $c, $record, $show_deleted = false) {
		return self::doSelectJoinDistinct($obj, $c, $record, $show_deleted, false);
	}

	/**
	 * Distinct select all records along with everything they're joined to (outer join)
	 * The join used will be an INNER join unless the criteria passed defines otherwise.
	 * @param Nina_Orm $obj The table to start joining from
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param string $record Record name
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return Nina_Orm
	 * @throws Nina_Exception_Orm If invalid mappings are found
	 */
	public static function doSelectJoinDistinctOuter(Nina_Orm $obj, Nina_Orm_Criteria $c, $record, $show_deleted = false) {
		return self::doSelectJoinDistinct($obj, $c, $record, $show_deleted, true);
	}

	/**
	 * Shared select join logic
	 * @param Nina_Orm $obj The table to start joining from
	 * @param Nina_Db_Statement $stmt The database statement
	 * @return array
	 */
	protected static function selectJoinHelper($obj, $stmt) {
		$result = $stmt->execute();
		if (!$result) return array();

		$ret = array();

		$foreign_keys = array();

		//Create an instance of each class defined via a foreign key, ready for hydration
		foreach($obj->getForeignKeys() as $class) {
			$foreign_obj = new $class();

			$as_position = strpos($foreign_obj->getTableName(), ' as ');

			if($as_position !== false)
				$table_name = substr($foreign_obj->getTableName(), $as_position + 4);
			else
				$table_name = $foreign_obj->getTableName();

			$foreign_keys[$table_name] = $class;
		}

		while ($row = $result->row()) {
			$o = clone $obj;
			$foreign_key_objects = array();

			foreach ($row as $field => $value) {
				$table_name = substr($field, 0, strpos($field, '.'));

				//If the table name is in our foreign keys then inject the field in there
				if(isset($foreign_keys[$table_name])) {
					if(!isset($foreign_key_objects[$table_name])) {
						$class = $foreign_keys[$table_name];
						$foreign_key_objects[$table_name] = new $class();
					}

					if($foreign_key_objects[$table_name]->hasColumn($field)) {
						$foreign_key_objects[$table_name]->injectField($field, $value);
					}
				}
				//Otherwise the field must belong to our core table
				elseif($o->hasColumn($field))
					$o->injectField($field, $value);
			}

			//Inject the hydrated foreign models ready for use without the need for additional queries!
			foreach($foreign_key_objects as $table_name => $foreign_obj) {
				$o->injectField($foreign_obj->getClass(), $foreign_obj);
			}

			$ret[] = $o;
		}

		return $ret;
	}

	/**
	 * Select a record along with everything it's joined to.
	 * The join used will be an INNER join unless the criteria passed defines otherwise.
	 * @param Nina_Orm $obj The table to start joining from
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return Nina_Orm
	 * @throws Nina_Exception_Orm If invalid mappings are found
	 */
	public static function doSelectJoinOne(Nina_Orm $obj, Nina_Orm_Criteria $c, $show_deleted = false) {
		$c->setLimit(1);

		if($record = self::doSelectJoin($obj, $c, $show_deleted))
			return $record[0];
		else
			return false;
	}

	/**
	 * Create a new record, inserted from the given list of values.
	 * @param Nina_Orm $obj Object to insert into
	 * @param array $fields Fields to insert
	 * @param boolean [$sanitise=false] Sanitise the data
	 * @return Nina_Orm
	 */
	public static function doInsert(Nina_Orm $obj, array $fields, $sanitise = false) {
		$now = Nina_Timestamp::now();
		$class = $obj->getClass();

		$fields = $obj->setInsertDomain($fields);

		if($obj->hasField('CREATED_AT') && !$obj->getCreatedAt()) {
			$obj->setCreatedAt($now);
			$fields[$obj->getField('CREATED_AT')] = $now;
		}

		if($obj->hasField('UPDATED_AT')) {
			$obj->setUpdatedAt($now);
			$fields[$obj->getField('UPDATED_AT')] = $now;
		}

		//Set the initial sequence value
		if($obj->hasField('SEQUENCE')) {
			$c = self::newCriteria();
			$c->addOrderBy($obj->getField('SEQUENCE'), Nina_Orm_Criteria::ORDER_DESC);
			if ($last_by_sequence = self::doSelectOne(new $class(), $c))
				$sequence = $last_by_sequence->getSequence()+1;
			else
				$sequence = 1;

			$obj->setSequence($sequence);
			$fields[$obj->getField('SEQUENCE')] = $sequence;
		}

		$placeholders = array();
		$values = array();
		$valid_fields = array();

		foreach ($fields as $field => $value) {
			if ($obj->hasColumn($field)) {
				//Update the object with the new value
				$obj->doSet($field, $value);

				//Prepare the data for SQL
				$valid_fields[] = self::addBackticks($field);
				$placeholders[] = "?";
				if($sanitise) $value = Nina_String::doSanitise($value);
				$values[] = $value;
			}
		}

		#Would be nice to test for validation earlier but we only have a complete ORM object when we get to here
		if($obj->valid()) {
			$query = "INSERT INTO " . $obj->getTableName() . PHP_EOL;
			$query .= " (" . implode("," . PHP_EOL, $valid_fields) . ")";
			$query .= " VALUES";
			$query .= " (" . implode("," . PHP_EOL, $placeholders) . ")";

			if (!$stmt = self::prepare($obj, $query))
				throw new Nina_Exception_Orm("Cannot INSERT record for " . $obj->getClass() . ".");

			$stmt->bind($values);
			$success = $stmt->execute();

			//Now the record has been inserted, we should be able to get a primary key
			$obj->setPk($obj->getDb()->insertId());
			$obj->setModifiedFields(array());
			return $obj;
		}
		else return false;
	}

	/**
	 * Update objects of the given table type with the given values where the given criteria matches.
	 * @param Nina_Orm $obj The mapping object
	 * @param array $fields A hash map of (field => value) to update
	 * @param Nina_Orm_Criteria $c The criteria to use
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @param boolean [$sanitise=false] Sanitise the data
	 * @return boolean
	 */
	public static function doUpdate(Nina_Orm $obj, array $fields, Nina_Orm_Criteria $c, $show_deleted = false, $sanitise = false) {
		$set = array();
		$values = array();
		$placeholders = array();

		if($obj->hasField('UPDATED_AT')) {
			$now = Nina_Timestamp::now();
			$obj->setUpdatedAt($now);
			$fields[$obj->getField('UPDATED_AT')] = $now;
		}

		$c->setDb($obj->getDb());
		$c = $obj->setShowDeleted($c, $show_deleted);
		$c = $obj->setSelectDomain($c);

		foreach ($fields as $field => $value) {
			if ($obj->hasColumn($field)) {
				//Update the object with the new value so that we can validate
				$obj->doSet($field, $value);

				//Prepare the data for SQL
				$placeholders[] = "?";
				$set[] = self::addBackticks($field) . " = ?";
				if($sanitise) $value = Nina_String::doSanitise($value);
				$values[] = $value;
			}
		}

		if (empty($set)) return false;

		$query = "UPDATE " . $obj->getTableName() . PHP_EOL;
		$query .= "SET" . PHP_EOL . implode("," . PHP_EOL, $set) . PHP_EOL;
		$query .= "WHERE" . PHP_EOL . $c->createWhereClause();
		if (!$stmt = self::prepare($obj, $query))
			throw new Nina_Exception_Orm("Could not UPDATE record for " . $obj->getClass() . ".");

		$stmt->bind($values);
		$c->commit($stmt);
		$success = $stmt->execute();
		$obj->setModifiedFields(array());
		return $success;
	}

	/**
	 * Create or update a record from the given list of values.
	 * @param Nina_Orm $obj Object to insert into
	 * @param array $fields Fields to insert
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @param boolean [$sanitise=false] Sanitise the data
	 * @return Nina_Orm
	 */
	public static function doReplace(Nina_Orm $obj, array $fields, $show_deleted = false, $sanitise = false) {

		$placeholders = array();
		$values = array();
		$valid_fields = array();

		$fields = $obj->setInsertDomain($fields);

		if($obj->hasField('UPDATED_AT'))
			$fields[$obj->getField('UPDATED_AT')] = Nina_Timestamp::now();

		foreach ($fields as $field => $value) {
			if ($obj->hasColumn($field)) {
				$valid_fields[] = self::addBackticks($field);
				$placeholders[] = "?";
				if($sanitise) $value = Nina_String::doSanitise($value);
				$values[] = $value;
			}
		}

		$query = "Insert INTO " . $obj->getTableName() . PHP_EOL;
		$query .= " (" . implode("," . PHP_EOL, $valid_fields) . ")";

		$query .= " VALUES";
		$query .= " (" . implode("," . PHP_EOL, $placeholders) . ")";
		$query .= " ON DUPLICATE KEY UPDATE";

		foreach($valid_fields as $field) {
			$query .= " ".$field." = VALUES(".$field."),";
		}

		$query = substr($query, 0, -1);

		if (!$stmt = self::prepare($obj, $query))
			throw new Nina_Exception_Orm("Cannot REPLACE record for " . $obj->getClass() . ".");

		$stmt->bind($values);
		$success = $stmt->execute();

		//Now the record has been inserted, we should be able to get a primary key
		$pk = $obj->getDb()->insertId();

		$obj->setModifiedFields(array());

		if($obj->isNew()) {
			if($obj->hasField('CREATED_AT') && !$obj->getCreatedAt()) {
				$obj->setCreatedAt(Nina_Timestamp::now());
				$obj->save();
			}
		}

		return $obj;
	}

	/**
	 * Delete records which match the given criteria.
	 * @param Nina_Orm $obj The object type to delete from
	 * @param Nina_Orm_Criteria $c The criteria to search
	 * @param boolean [$by_pk = false]
	 * @return boolean
	 */
	public static function doDelete(Nina_Orm $obj, Nina_Orm_Criteria $c, $by_pk = false) {
		$c->setDb($obj->getDb());
		$c->addTable($obj->getClass());
		$c = $obj->setSelectDomain($c);

		if($obj->hasField('DELETED_AT')) {
			$query = 'UPDATE ' . $obj->getTableName() . PHP_EOL;
			$query .= 'SET	' . $obj->getField('DELETED_AT') . ' = ' . Nina_Timestamp::now() . PHP_EOL;
			$query .= 'WHERE' . PHP_EOL . $c->createWhereClause($by_pk);
		}
		else {
			$query = 'DELETE ' .$obj->getTableName() . '.*' . ' FROM ' . $c->createFromClause() . PHP_EOL;
			$query .= 'WHERE' . PHP_EOL . $c->createWhereClause($by_pk);
		}

		if (!$stmt = self::prepare($obj, $query))
			throw new Nina_Exception_Orm("Could not DELETE record for " . $obj->getClass() . ".");

		$c->commit($stmt);
		$success = $stmt->execute();
		$obj->setDeleted(true);
		return $success;
	}

	/**
	 * Truncate a table
	 * @param Nina_Orm $obj Object to truncate
	 * @return Nina_Orm
	 */
	public static function doTruncate(Nina_Orm $obj) {
		$query = "TRUNCATE TABLE ".$obj->getTableName();

		if (!$stmt = self::prepare($obj, $query))
			throw new Nina_Exception_Orm("Cannot TRUNCATE table for " . $obj->getClass() . ".");

		$success = $stmt->execute();
		return $obj;
	}

	/**
	 * Builds select SQL from a given criteria and ORM mapping object.
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @param boolean [$distinct=false] Do a distinct select
	 * @return Nina_Db_Adaptor_Statement
	 */
	protected static function createSelectStatement(Nina_Orm $obj, Nina_Orm_Criteria $c, $show_deleted = false, $distinct = false) {
		$cloned_criteria = clone $c;
		$cloned_criteria->setDb($obj->getDb());
		$cloned_criteria->addTable($obj->getClass());
		$cloned_criteria = $obj->setShowDeleted($cloned_criteria, $show_deleted);
		$cloned_criteria = $obj->setSelectDomain($cloned_criteria);

		$sql = "SELECT" . PHP_EOL;

		if($distinct) $sql .= "DISTINCT" . PHP_EOL;

		$fields = array();

		if($c->isSelectFromSubquery())
			$sql .= '* ';
		else {

			//Add all the fields of this model to the select
			foreach ($obj->getFieldNames() as $field) {
				$select_field = $c->replaceSelectField($field, true);
				$fields[] = $select_field . " AS `" . str_replace('`', '', $field) . "`";
			}
			//Any special select replacements (e.g. count, max, min etc) that aren't on the main model (and hence the primary table) need to be added in manually as they may well be used for sorting, grouping etc
			foreach($c->getSpecialSelects() as $field => $operation) {
				$fields[] = $operation .'(' . $field . ')' . " AS `" . str_replace('`', '', $field) . "`";
			}

			$sql .= implode("," . PHP_EOL, $fields) . PHP_EOL;
		}

		$sql .= "FROM" . PHP_EOL . $cloned_criteria->createFromClause() . PHP_EOL;
		$sql .= "WHERE" . PHP_EOL;

		$sql .= $cloned_criteria->createWhereClause();

		if ($stmt = self::prepare($obj, $sql))
			$cloned_criteria->commit($stmt);

		return $stmt;
	}

	/**
	 * Builds distinct select SQL from a given criteria and ORM mapping object.
	 * In order to get all the other fields, this is achieved using a 'group by' command
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param string $record Record name
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return Nina_Db_Adaptor_Statement
	 */
	protected static function createSelectDistinctStatement(Nina_Orm $obj, Nina_Orm_Criteria $c, $record, $show_deleted = false) {
		$c->addGroupBy($record);
		return self::createSelectStatement($obj, $c, $show_deleted);
	}

	/**
	 * Builds count SQL from a given criteria and ORM mapping object.
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return Nina_Db_Adaptor_Statement
	 */
	protected static function createCountStatement(Nina_Orm $obj, Nina_Orm_Criteria $c, $show_deleted = false, $distinct = false) {
		$c->setDb($obj->getDb());
		$class_name = $obj->getClass();
		$c->addTable($class_name);
		$c = $obj->setShowDeleted($c, $show_deleted);
		$c = $obj->setSelectDomain($c);

		$sql = "SELECT COUNT(";

		if($distinct) $sql .= "DISTINCT ";

		$sql .= constant("$class_name::ID").") as row_count FROM" . PHP_EOL . $c->createFromClause() . PHP_EOL;
		$sql .= "WHERE" . PHP_EOL;

		$sql .= $c->createWhereClause();

		if ($stmt = self::prepare($obj, $sql))
			$c->commit($stmt);

		return $stmt;
	}

	/**
	 * Builds count SQL from a given criteria and ORM mapping object.
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param string $record Record name
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @return Nina_Db_Adaptor_Statement
	 */
	protected static function createCountDistinctStatement(Nina_Orm $obj, Nina_Orm_Criteria $c, $record, $show_deleted = false) {
		$c->setDb($obj->getDb());
		$class_name = $obj->getClass();
		$c->addTable($class_name);
		$c = $obj->setShowDeleted($c, $show_deleted);
		$c = $obj->setSelectDomain($c);

		$sql = "SELECT COUNT(DISTINCT ". $record .") as row_count FROM" . PHP_EOL . $c->createFromClause() . PHP_EOL;
		$sql .= "WHERE" . PHP_EOL;

		$sql .= $c->createWhereClause();

		if ($stmt = self::prepare($obj, $sql))
			$c->commit($stmt);

		return $stmt;
	}

	/**
	 * Builds select SQL from a given criteria and ORM mapping object for a join.
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @param boolean [$outer=false] Do outer join
	 * @return Nina_Db_Adaptor_Statement
	 */
	protected static function createSelectJoinStatement(Nina_Orm $obj, Nina_Orm_Criteria $c, $show_deleted = false, $outer = false) {
		$c->setDb($obj->getDb());
		$class_name = $obj->getClass();
		$c->addTable($class_name);
		$c = $obj->setShowDeleted($c, $show_deleted);
		$c = $obj->setSelectDomain($c);

		$join = ($outer) ? Nina_Orm_Join::LEFT_OUTER : Nina_Orm_Join::INNER;

		$sql = "SELECT" . PHP_EOL;
		$columns = array();
		foreach ($obj->getFieldNames(true) as $field => $column) {
			if ($column instanceof self) {
				foreach ($column->getFieldNames() as $joined_column) {
					$columns[] = $joined_column . " AS `" . $joined_column . "`";
				}

				$c->addJoin($obj->getClass() . '::' . strtoupper($field), $column->getClass() . '::ID', $join);
			}
			else
				$columns[] = $column . " AS `" . $column . "`";
		}
		$sql .= implode("," . PHP_EOL, $columns) . PHP_EOL;
		$sql .= "FROM" . PHP_EOL . $c->createFromClause() . PHP_EOL;
		$sql .= "WHERE" . PHP_EOL;

		$sql .= $c->createWhereClause();

		if ($stmt = self::prepare($obj, $sql))
			$c->commit($stmt);

		return $stmt;
	}

	/**
	 * Builds distinct select SQL from a given criteria and ORM mapping object.
	 * In order to get all the other fields, this is achieved using a 'group by' command
	 * @param Nina_Orm $obj Used for hydration
	 * @param Nina_Orm_Criteria $c The search criteria
	 * @param string $record Record name
	 * @param boolean [$show_deleted=false] Show deleted records
	 * @param boolean [$outer=false] Do outer join
	 * @return Nina_Db_Adaptor_Statement
	 */
	protected static function createSelectJoinDistinctStatement(Nina_Orm $obj, Nina_Orm_Criteria $c, $record, $show_deleted = false, $outer = false) {
		$c->addGroupBy($record);
		return self::createSelectJoinStatement($obj, $c, $show_deleted, $outer);
	}

	/**
	 * Add backticks to a field name
	 * @param string $field Field name to add backticks to
	 * @return string
	 */
	protected static function addBackticks($field) {
		return '`' . str_replace('.', '`.`', $field) . '`';
	}

	/**
	 * Removes the table name from a field if present.
	 * @param string $table Table name
	 * @param string $field Field name
	 * @return string
	 */
	public static function stripTableName($table, $field) {
		$table_len = strlen($table);

		if (substr($field, 0, $table_len + 1) == $table . ".")
			$field = substr($field, $table_len + 1);

		return $field;
	}

	/**
	 * Prepare SQL for use in as many databases as possible.
	 * @param Nina_Orm $obj Input object
	 * @param string $sql Input SQL
	 * @return Nina_Db_Adaptor_Statement
	 */
	public static function prepare(Nina_Orm $obj, $sql) {
		//Fixes the dot syntax for write processes.
		$table = '(?:`?[a-zA-Z0-9_\-]+`?\.)?`?[a-zA-Z0-9_\-]+`?(?:\s+(?:AS\s+)?`?\S+`?)?';
		$field = '(?:`?[a-zA-Z0-9_\-]+`?\.){0,2}`?[a-zA-Z0-9_\-]+`?(?:\s+(?:AS\s+)?`?\S+`?)?';
		$insert = '/^(\s*INSERT\s+INTO\s+)(' . $table . ')(\s*\()((?:' . $field . '\s*,\s*)*'. $field . ')(\).*)$/is';
		$update = '/^(\s*UPDATE\s+)(' . $table . ')(\s+SET\s+)((?:' . $field . '\s*=.*?,\s*)*' . $field . '\s*=(?:.(?!WHERE))*)(.*)$/is';
		if (preg_match($insert, $sql, $matches)) {
			unset($matches[0]);
			$tbl = $matches[2];
			$fields =& $matches[4];
			$split_fields = preg_split('/\s*,\s*/', $fields);
			foreach ($split_fields as $i => $v) {
				$split_fields[$i] = self::stripTableName($tbl, $v);
			}
			$fields = implode(", ", $split_fields);
			$sql = implode("", $matches);
		}
		elseif (preg_match($update, $sql, $matches)) {
			unset($matches[0]);
			$tbl = $matches[2];
			$fields =& $matches[4];
			$split_fields = preg_split('/\s*,\s*(?=' . $field . '\s*=)/', $fields);
			foreach ($split_fields as $i => $v) {
				$split_fields[$i] = self::stripTableName($tbl, $v);
			}
			$fields = implode(", ", $split_fields);
			$sql = implode("", $matches);
		}

		$stmt = $obj->getDb()->prepare($sql);

		return $stmt;
	}
}
